package org.halk.test.readtxt;

import org.junit.Test;

import java.io.IOException;
import java.nio.file.Files;
import java.nio.file.Paths;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

/**
 * 读取一本txt文件，按照段落、行号然后插入到mysql中,
 *
 * 最终需要的是，对文本进行全文检索，返回目标段落的上下各3条数据
 * mysql不是个全文检索，速度很慢
 *
 WITH
 -- 为原始数据生成行号（按 id 排序）
 numbered_data AS (SELECT id,
 content,
 ROW_NUMBER() OVER (ORDER BY id) AS row_num
 FROM book_line),
 -- 目标行号（示例：通过条件筛选出的行号）
 target_rows AS (SELECT row_num
 FROM numbered_data
 WHERE content like '%玲珑%' -- 替换为实际筛选条件
 )
 -- 获取目标行号附近的数据并去重
 SELECT DISTINCT nd.id, nd.content
 FROM numbered_data nd
 JOIN target_rows tr
 ON nd.row_num BETWEEN tr.row_num - 3 AND tr.row_num + 3
 ORDER BY nd.id;
 *
 * @Author haozhu7
 * @Date 2025/5/16 11:22
 **/
public class ReadTxtImportMysql {

    private static final String URL = "jdbc:mysql://127.0.0.1:3306/halk?useSSL=true&useUnicode=true&characterEncoding=utf8";
    private static final String USER = "root";
    private static final String PASSWORD = "Abcd@1234";
    private static final String TXT_PATH = "H:\\test\\仙界修仙.txt";

    @Test
    public void insertMysql() throws SQLException, IOException {

        //获取连接
        Connection connect = DriverManager.getConnection(URL, USER, PASSWORD);

        //查询获取段落
        Map<String, StringBuilder> stringStringBuilderMap = readTxt(TXT_PATH);

        //插入章节内容
        insertBookContent(stringStringBuilderMap, connect);

        //插入每行内容
        insertBookLine(connect);

        System.out.println();
    }

    /**
     * 插入每行内容
     * @param connect
     */
    private static void insertBookLine(Connection connect) throws IOException {
        List<String> lines = Files.readAllLines(Paths.get(TXT_PATH));


        ArrayList<String> sqlList = new ArrayList<>();
        StringBuilder sql = new StringBuilder("insert into book_line(id, line_num, content) values ");
        int id = 1;
        for (String line : lines) {
            if (line.trim().isEmpty()) {
                continue;
            }

            sql.append("(").append(id).append(",'").append(id).append("','").append(line).append("'),");

            id++;
            if (id % 1000 == 0 || id >= lines.size()) {
                sqlList.add(sql.substring(0, sql.length() - 1));
                sql = new StringBuilder("insert into book_line(id, line_num, content) values ");
            }

        }

        sqlList.forEach(x -> {
            try {
                connect.prepareStatement(x).executeUpdate();
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        });
    }

    /**
     * 插入章节内容
     * @param stringStringBuilderMap
     * @param connect
     */
    private static void insertBookContent(Map<String, StringBuilder> stringStringBuilderMap, Connection connect) {
        ArrayList<String> sqlList = new ArrayList<>();
        StringBuilder sql = new StringBuilder("insert into book(id, chapter_name, content) values ");
        int id = 1;
        for (Map.Entry<String, StringBuilder> entry : stringStringBuilderMap.entrySet()) {
            String chapter = entry.getKey();
            StringBuilder content = entry.getValue();
            sql.append("(").append(id).append(",'").append(chapter).append("','").append(content).append("'),");


            id++;
            if (id % 1000 == 0 || id >= stringStringBuilderMap.size()) {
                sqlList.add(sql.substring(0, sql.length() - 1));
                sql = new StringBuilder("insert into book(id, chapter_name, content) values ");
                id = 1;
            }

        }

        sqlList.forEach(x -> {
            try {
                connect.prepareStatement(x).executeUpdate();
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        });
    }


    /**
     * 读取txt的段落
     * @return
     * @throws IOException
     */
    public Map<String, StringBuilder> readTxt(String txtPath) throws IOException {
        List<String> lines = Files.readAllLines(Paths.get(txtPath));

        Map<String, StringBuilder> chapters = new LinkedHashMap<>();
        String currentChapter = null;
        StringBuilder chapterContent = new StringBuilder();
        for (String line : lines) {
            if (isChapterHeader(line)) { // 判断是否是章节标题
                if (currentChapter != null) {
                    chapters.put(currentChapter, chapterContent);
                }
                currentChapter = line.trim();
                chapterContent = new StringBuilder();
            } else {
                chapterContent.append(line);
            }
        }
        // 添加最后一个章节
        if (currentChapter != null) {
            chapters.put(currentChapter, chapterContent);
        }
        return chapters;
    }

    // 判断是否是章节标题，可以根据实际小说格式调整正则表达式
    private boolean isChapterHeader(String line) {
        return line.trim().startsWith("第") && (line.contains("章") || line.contains("回"));
    }

}
